{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## In-Vantage Scoring via PMML\n",
    "\n",
    "As part of the PIMA Diabetes model, we produce a PMML representation of the xgboost model with the following code\n",
    "\n",
    "```python\n",
    "from nyoka import xgboost_to_pmml\n",
    "...\n",
    "xgboost_to_pmml(pipeline=model, col_names=feature_names, target_name=target_name, pmml_f_name=\"artifacts/output/model.pmml\")\n",
    "```\n",
    "\n",
    "You can deploy that In-Vantage via the AnalyticOps UI, just as with RESTFul and Batch deployments. This notebook shows you how you can then use this to score models via SQL. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "import getpass\n",
    "\n",
    "from teradataml import create_context, remove_context\n",
    "from teradataml.context.context import get_connection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      "AOA_DEMO Password ··········\n"
     ]
    }
   ],
   "source": [
    "password = getpass.getpass(\"Password\")\n",
    "\n",
    "engine = create_context(host=\"3.238.151.85\", username=\"AOA_DEMO\", password=password)\n",
    "conn = get_connection()\n",
    "cursor = engine.raw_connection().cursor()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## View Published Models\n",
    "\n",
    "We can view the models published to vantage by querying the table they are published to. Note this information is available via the AOA APIs also"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>model_version</th>\n",
       "      <th>model_id</th>\n",
       "      <th>project_id</th>\n",
       "      <th>deployed_at</th>\n",
       "      <th>model</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>f7741c1f-ca5e-4c16-a1fb-1bd4ea7e3df4</td>\n",
       "      <td>07701a36-8ea1-4156-94be-20836f38d0a7</td>\n",
       "      <td>23e1df4b-b630-47a1-ab80-7ad5385fcd8d</td>\n",
       "      <td>2020-11-23 21:08:42.460</td>\n",
       "      <td>b'&lt;?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?&gt;\\n&lt;PMML xmlns=\"http://www.dmg.org/PMML-4_4\" xmlns:data=\"http://jpmml.org/jpmml-model/InlineTable\" version=\"4.3\"&gt;\\n\\t&lt;Header&gt;\\n\\t\\t&lt;Application name=\"JPMML-SkLearn\" version=\"1.6.3\"/&gt;\\n\\t\\t&lt;T...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>84cd9235-2cce-47f5-9c3d-3751d89b4520</td>\n",
       "      <td>03c9a01f-bd46-4e7c-9a60-4282039094e6</td>\n",
       "      <td>23e1df4b-b630-47a1-ab80-7ad5385fcd8d</td>\n",
       "      <td>2020-11-26 11:32:49.530</td>\n",
       "      <td>b'&lt;?xml version=\"1.0\" encoding=\"UTF-8\"?&gt;\\n&lt;PMML xmlns=\"http://www.dmg.org/PMML-4_4\" version=\"4.4\"&gt;\\n    &lt;Header copyright=\"Copyright (c) 2018 Software AG\" description=\"Default Description\"&gt;\\n        &lt;Application name=\"Nyoka\" version=\"4.3.0\"/&gt;\\n  ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>38e4693b-141b-4230-892c-a9affbf69671</td>\n",
       "      <td>03c9a01f-bd46-4e7c-9a60-4282039094e6</td>\n",
       "      <td>23e1df4b-b630-47a1-ab80-7ad5385fcd8d</td>\n",
       "      <td>2020-11-26 12:52:09.040</td>\n",
       "      <td>b'&lt;?xml version=\"1.0\" encoding=\"UTF-8\"?&gt;\\n&lt;PMML xmlns=\"http://www.dmg.org/PMML-4_4\" version=\"4.4\"&gt;\\n    &lt;Header copyright=\"Copyright (c) 2018 Software AG\" description=\"Default Description\"&gt;\\n        &lt;Application name=\"Nyoka\" version=\"4.3.0\"/&gt;\\n  ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>80b3502f-a171-4c33-a524-92e43fd59545</td>\n",
       "      <td>03c9a01f-bd46-4e7c-9a60-4282039094e6</td>\n",
       "      <td>23e1df4b-b630-47a1-ab80-7ad5385fcd8d</td>\n",
       "      <td>2020-11-24 12:21:22.520</td>\n",
       "      <td>b'&lt;?xml version=\"1.0\" encoding=\"UTF-8\"?&gt;\\n&lt;PMML xmlns=\"http://www.dmg.org/PMML-4_4\" version=\"4.4\"&gt;\\n    &lt;Header copyright=\"Copyright (c) 2018 Software AG\" description=\"Default Description\"&gt;\\n        &lt;Application name=\"Nyoka\" version=\"4.3.0\"/&gt;\\n  ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5ef8f6cc-b934-46f1-81e5-b629e52241a3</td>\n",
       "      <td>03c9a01f-bd46-4e7c-9a60-4282039094e6</td>\n",
       "      <td>23e1df4b-b630-47a1-ab80-7ad5385fcd8d</td>\n",
       "      <td>2020-11-24 11:20:52.200</td>\n",
       "      <td>b'&lt;?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?&gt;\\n&lt;PMML xmlns=\"http://www.dmg.org/PMML-4_4\" xmlns:data=\"http://jpmml.org/jpmml-model/InlineTable\" version=\"4.4\"&gt;\\n\\t&lt;Header&gt;\\n\\t\\t&lt;Application name=\"JPMML-SkLearn\" version=\"1.6.7\"/&gt;\\n\\t\\t&lt;T...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>7d4bf85a-b58e-4e5d-a9e3-0b73c777ce31</td>\n",
       "      <td>03c9a01f-bd46-4e7c-9a60-4282039094e6</td>\n",
       "      <td>23e1df4b-b630-47a1-ab80-7ad5385fcd8d</td>\n",
       "      <td>2020-11-23 13:00:21.920</td>\n",
       "      <td>b'&lt;?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?&gt;\\n&lt;PMML xmlns=\"http://www.dmg.org/PMML-4_4\" xmlns:data=\"http://jpmml.org/jpmml-model/InlineTable\" version=\"4.4\"&gt;\\n\\t&lt;Header&gt;\\n\\t\\t&lt;Application name=\"JPMML-SkLearn\" version=\"1.6.7\"/&gt;\\n\\t\\t&lt;T...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          model_version                              model_id  \\\n",
       "0  f7741c1f-ca5e-4c16-a1fb-1bd4ea7e3df4  07701a36-8ea1-4156-94be-20836f38d0a7   \n",
       "1  84cd9235-2cce-47f5-9c3d-3751d89b4520  03c9a01f-bd46-4e7c-9a60-4282039094e6   \n",
       "2  38e4693b-141b-4230-892c-a9affbf69671  03c9a01f-bd46-4e7c-9a60-4282039094e6   \n",
       "3  80b3502f-a171-4c33-a524-92e43fd59545  03c9a01f-bd46-4e7c-9a60-4282039094e6   \n",
       "4  5ef8f6cc-b934-46f1-81e5-b629e52241a3  03c9a01f-bd46-4e7c-9a60-4282039094e6   \n",
       "5  7d4bf85a-b58e-4e5d-a9e3-0b73c777ce31  03c9a01f-bd46-4e7c-9a60-4282039094e6   \n",
       "\n",
       "                             project_id             deployed_at  \\\n",
       "0  23e1df4b-b630-47a1-ab80-7ad5385fcd8d 2020-11-23 21:08:42.460   \n",
       "1  23e1df4b-b630-47a1-ab80-7ad5385fcd8d 2020-11-26 11:32:49.530   \n",
       "2  23e1df4b-b630-47a1-ab80-7ad5385fcd8d 2020-11-26 12:52:09.040   \n",
       "3  23e1df4b-b630-47a1-ab80-7ad5385fcd8d 2020-11-24 12:21:22.520   \n",
       "4  23e1df4b-b630-47a1-ab80-7ad5385fcd8d 2020-11-24 11:20:52.200   \n",
       "5  23e1df4b-b630-47a1-ab80-7ad5385fcd8d 2020-11-23 13:00:21.920   \n",
       "\n",
       "                                                                                                                                                                                                                                                       model  \n",
       "0  b'<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?>\\n<PMML xmlns=\"http://www.dmg.org/PMML-4_4\" xmlns:data=\"http://jpmml.org/jpmml-model/InlineTable\" version=\"4.3\">\\n\\t<Header>\\n\\t\\t<Application name=\"JPMML-SkLearn\" version=\"1.6.3\"/>\\n\\t\\t<T...  \n",
       "1  b'<?xml version=\"1.0\" encoding=\"UTF-8\"?>\\n<PMML xmlns=\"http://www.dmg.org/PMML-4_4\" version=\"4.4\">\\n    <Header copyright=\"Copyright (c) 2018 Software AG\" description=\"Default Description\">\\n        <Application name=\"Nyoka\" version=\"4.3.0\"/>\\n  ...  \n",
       "2  b'<?xml version=\"1.0\" encoding=\"UTF-8\"?>\\n<PMML xmlns=\"http://www.dmg.org/PMML-4_4\" version=\"4.4\">\\n    <Header copyright=\"Copyright (c) 2018 Software AG\" description=\"Default Description\">\\n        <Application name=\"Nyoka\" version=\"4.3.0\"/>\\n  ...  \n",
       "3  b'<?xml version=\"1.0\" encoding=\"UTF-8\"?>\\n<PMML xmlns=\"http://www.dmg.org/PMML-4_4\" version=\"4.4\">\\n    <Header copyright=\"Copyright (c) 2018 Software AG\" description=\"Default Description\">\\n        <Application name=\"Nyoka\" version=\"4.3.0\"/>\\n  ...  \n",
       "4  b'<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?>\\n<PMML xmlns=\"http://www.dmg.org/PMML-4_4\" xmlns:data=\"http://jpmml.org/jpmml-model/InlineTable\" version=\"4.4\">\\n\\t<Header>\\n\\t\\t<Application name=\"JPMML-SkLearn\" version=\"1.6.7\"/>\\n\\t\\t<T...  \n",
       "5  b'<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?>\\n<PMML xmlns=\"http://www.dmg.org/PMML-4_4\" xmlns:data=\"http://jpmml.org/jpmml-model/InlineTable\" version=\"4.4\">\\n\\t<Header>\\n\\t\\t<Application name=\"JPMML-SkLearn\" version=\"1.6.7\"/>\\n\\t\\t<T...  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.options.display.max_colwidth = 250\n",
    "pd.read_sql(\"select * from aoa_ivsm_models\", conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## On-Demand Scoring"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>PatientId</th>\n",
       "      <th>score_result</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>{\"probability_0\":0.9886043799233987,\"probability_1\":0.011395620076601292,\"predicted_HasDiabetes\":0}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "      <td>{\"probability_0\":0.9980198370119622,\"probability_1\":0.0019801629880378813,\"predicted_HasDiabetes\":0}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8</td>\n",
       "      <td>{\"probability_0\":0.05094995570429306,\"probability_1\":0.9490500442957069,\"predicted_HasDiabetes\":1}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>9</td>\n",
       "      <td>{\"probability_0\":0.13574050732643506,\"probability_1\":0.8642594926735649,\"predicted_HasDiabetes\":1}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>13</td>\n",
       "      <td>{\"probability_0\":0.5286287238760723,\"probability_1\":0.4713712761239277,\"predicted_HasDiabetes\":0}</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   PatientId  \\\n",
       "0          1   \n",
       "1          3   \n",
       "2          8   \n",
       "3          9   \n",
       "4         13   \n",
       "\n",
       "                                                                                           score_result  \n",
       "0   {\"probability_0\":0.9886043799233987,\"probability_1\":0.011395620076601292,\"predicted_HasDiabetes\":0}  \n",
       "1  {\"probability_0\":0.9980198370119622,\"probability_1\":0.0019801629880378813,\"predicted_HasDiabetes\":0}  \n",
       "2    {\"probability_0\":0.05094995570429306,\"probability_1\":0.9490500442957069,\"predicted_HasDiabetes\":1}  \n",
       "3    {\"probability_0\":0.13574050732643506,\"probability_1\":0.8642594926735649,\"predicted_HasDiabetes\":1}  \n",
       "4     {\"probability_0\":0.5286287238760723,\"probability_1\":0.4713712761239277,\"predicted_HasDiabetes\":0}  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query=\"\"\"\n",
    "SELECT * FROM IVSM_SCORE(\n",
    "    on PIMA_TEST\n",
    "    on (SELECT model_id, model FROM aoa_ivsm_models WHERE model_version = '84cd9235-2cce-47f5-9c3d-3751d89b4520') AS ModelTable DIMENSION\n",
    "    using\n",
    "        ModelID('03c9a01f-bd46-4e7c-9a60-4282039094e6')\n",
    "        ColumnsToPreserve('PatientId')\n",
    "        ModelType('PMML')\n",
    ") sc;\n",
    "\"\"\"\n",
    "pd.read_sql(query, conn).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
